package org.morningangel.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Date;
import java.util.List;

import javax.naming.NamingException;

import org.morningangel.bean.User;
import org.morningangel.dao.UserDao;
import org.morningangel.util.DBHelperUtil;

public class UserDaoImpl implements UserDao {

	@Override
	public Integer register(User user) {
		Connection conn = null;
		PreparedStatement ps = null;
		Integer suNum = 0;
		try {
			conn = DBHelperUtil.getConnection();
			// 打开事务
			conn.setAutoCommit(false);
			String sql = "insert into tb_user (username, phone, email, password, qq, weibo, registerTime) values (?, ?, ?, ?, ?, ?, ?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPhone());
			ps.setString(3, user.getEmail());
			ps.setString(4, user.getPassword());
			ps.setString(5, user.getQq());
			ps.setString(6, user.getWeibo());
			ps.setTimestamp(7, new Timestamp(user.getRegisterTime().getTime()));
			suNum = ps.executeUpdate();
			// 提交事务
			conn.commit();
			// 关闭事务
			conn.setAutoCommit(true);
			System.out.println("suNum:"+suNum);
			
			if(suNum!=0){//若成功注册,则添加记录到tb_remind表
				String prodecureSql="{call remindtime(?,?)}";
				CallableStatement cs=conn.prepareCall(prodecureSql);//调用存储过程
				cs.setString(1,user.getPhone());
				cs.registerOutParameter(2, Types.INTEGER);
				cs.execute();
				
				cs.close();
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		DBHelperUtil.close(ps, conn);

		return suNum;
	}

	@Override
	public User login(String account) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		User  user = null;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "select id, username, phone, email, password, qq, weibo, registerTime from tb_user where phone = ? or email = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, account);
			ps.setString(2, account);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new User();
				setUser(rs, user);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(rs, ps, conn);
		
		return user;
	}

	@Override
	public User loginByQQ(String qq) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = null;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "select id, username, phone, email, password, qq, weibo, registerTime from tb_user where qq = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, qq);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new User();
				setUser(rs, user);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(rs, ps, conn);
		
		return user;
	}

	@Override
	public User loginByWeibo(String weibo) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = null;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "select id, username, phone, email, password, qq, weibo, registerTime from tb_user where weibo = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, weibo);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new User();
				setUser(rs, user);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(rs, ps, conn);
		
		return user;
	}

	@Override
	public Integer update(User user) {
		Connection conn = null;
		PreparedStatement ps = null;
		Integer suNum = 0;
		try {
			conn = DBHelperUtil.getConnection();
			//打开事务
			conn.setAutoCommit(false);
			String sql = "update tb_user set username = ?, phone = ?, email = ?, password = ?, qq = ?, weibo=? where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPhone());
			ps.setString(3, user.getEmail());
			ps.setString(4, user.getPassword());
			ps.setString(5, user.getQq());
			ps.setString(6, user.getWeibo());
			ps.setInt(7, user.getId());
			suNum = ps.executeUpdate();
			//提交事务
			conn.commit();
			//关闭事务
			conn.setAutoCommit(true);
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(ps, conn);
		
		return suNum;

	}

	@Override
	public User getById(Integer id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = null;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "select id, username, phone, email, password, qq, weibo, registerTime from tb_user where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new User();
				setUser(rs, user);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(rs, ps, conn);
		
		return user;
	}

	@Override
	public List<User> listPage(Integer pageNum, Integer pageSize) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<User> search(String key) {
		// TODO Auto-generated method stub
		return null;
	}
	
	//将查询结果ResultSet中的值封装到对象中
	private void setUser(ResultSet rs, User u) throws SQLException {
		u.setId(rs.getInt(1));
		u.setUsername(rs.getString(2));
		u.setPhone(rs.getString(3));
		u.setEmail(rs.getString(4));
		u.setPassword(rs.getString(5));
		u.setQq(rs.getString(6));
		u.setWeibo(rs.getString(7));
		u.setRegisterTime(new Date(rs.getTimestamp(8).getTime()));
	}

	@Override
	public Integer count() {
		// TODO Auto-generated method stub
		return null;
	}

}
